
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 10/20/2011 23:52:58
-- Generated from EDMX file: E:\project\WebLab\WebLab\ReportingCenterSite\ReportingCenterContent.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [ReportingCenterContent];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_ReportCategoryReport]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ReportSet] DROP CONSTRAINT [FK_ReportCategoryReport];
GO
IF OBJECT_ID(N'[dbo].[FK_TeamDashboard]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[DashboardSet] DROP CONSTRAINT [FK_TeamDashboard];
GO
IF OBJECT_ID(N'[dbo].[FK_TeamIssue]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[IssueSet] DROP CONSTRAINT [FK_TeamIssue];
GO
IF OBJECT_ID(N'[dbo].[FK_TeamMisc]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[MiscSet] DROP CONSTRAINT [FK_TeamMisc];
GO
IF OBJECT_ID(N'[dbo].[FK_TeamNews]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[NewsSet] DROP CONSTRAINT [FK_TeamNews];
GO
IF OBJECT_ID(N'[dbo].[FK_TeamPeople]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[PeopleSet] DROP CONSTRAINT [FK_TeamPeople];
GO
IF OBJECT_ID(N'[dbo].[FK_TeamReportCategory]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ReportCategorySet] DROP CONSTRAINT [FK_TeamReportCategory];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[DashboardSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[DashboardSet];
GO
IF OBJECT_ID(N'[dbo].[IssueSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[IssueSet];
GO
IF OBJECT_ID(N'[dbo].[MiscSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[MiscSet];
GO
IF OBJECT_ID(N'[dbo].[NewsSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[NewsSet];
GO
IF OBJECT_ID(N'[dbo].[PeopleSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PeopleSet];
GO
IF OBJECT_ID(N'[dbo].[ReportCategorySet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ReportCategorySet];
GO
IF OBJECT_ID(N'[dbo].[ReportSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ReportSet];
GO
IF OBJECT_ID(N'[dbo].[TeamSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TeamSet];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'TeamSet'
CREATE TABLE [dbo].[TeamSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [ShortTeamName] nvarchar(max)  NOT NULL,
    [LongTeamName] nvarchar(max)  NOT NULL,
    [TeamIntroduction] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'ReportSet'
CREATE TABLE [dbo].[ReportSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [ReportName] nvarchar(max)  NOT NULL,
    [ReportURL] nvarchar(max)  NOT NULL,
    [ReportDescription] nvarchar(max)  NOT NULL,
    [ReportCategoryId] int  NOT NULL,
    [LOB] nvarchar(max)  NULL,
    [Purpose] nvarchar(max)  NULL,
    [Scope] nvarchar(max)  NULL,
    [NumOfKPI] nvarchar(max)  NULL,
    [EffortType] nvarchar(max)  NULL,
    [ReportingCycles] nvarchar(max)  NULL,
    [ReportType] nvarchar(max)  NULL,
    [CapableToBeAuto] nvarchar(max)  NULL,
    [TargetAudience] nvarchar(max)  NULL,
    [Owners] nvarchar(max)  NULL,
    [Comments] nvarchar(max)  NULL,
    [Created] datetime  NULL
);
GO

-- Creating table 'ReportCategorySet'
CREATE TABLE [dbo].[ReportCategorySet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [ReportCategoryName] nvarchar(max)  NOT NULL,
    [TeamId] int  NOT NULL
);
GO

-- Creating table 'IssueSet'
CREATE TABLE [dbo].[IssueSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Content] nvarchar(max)  NOT NULL,
    [TeamId] int  NOT NULL,
    [Created] datetime  NULL
);
GO

-- Creating table 'PeopleSet'
CREATE TABLE [dbo].[PeopleSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Alias] nvarchar(max)  NOT NULL,
    [TeamId] int  NOT NULL
);
GO

-- Creating table 'NewsSet'
CREATE TABLE [dbo].[NewsSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Content] nvarchar(max)  NOT NULL,
    [TeamId] int  NOT NULL,
    [Created] datetime  NULL
);
GO

-- Creating table 'MiscSet'
CREATE TABLE [dbo].[MiscSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Content] nvarchar(max)  NOT NULL,
    [TeamId] int  NOT NULL,
    [Created] datetime  NULL
);
GO

-- Creating table 'DashboardSet'
CREATE TABLE [dbo].[DashboardSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [ReportServer] nvarchar(max)  NOT NULL,
    [ReportPath] nvarchar(max)  NOT NULL,
    [TeamId] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'TeamSet'
ALTER TABLE [dbo].[TeamSet]
ADD CONSTRAINT [PK_TeamSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ReportSet'
ALTER TABLE [dbo].[ReportSet]
ADD CONSTRAINT [PK_ReportSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ReportCategorySet'
ALTER TABLE [dbo].[ReportCategorySet]
ADD CONSTRAINT [PK_ReportCategorySet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'IssueSet'
ALTER TABLE [dbo].[IssueSet]
ADD CONSTRAINT [PK_IssueSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'PeopleSet'
ALTER TABLE [dbo].[PeopleSet]
ADD CONSTRAINT [PK_PeopleSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'NewsSet'
ALTER TABLE [dbo].[NewsSet]
ADD CONSTRAINT [PK_NewsSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'MiscSet'
ALTER TABLE [dbo].[MiscSet]
ADD CONSTRAINT [PK_MiscSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'DashboardSet'
ALTER TABLE [dbo].[DashboardSet]
ADD CONSTRAINT [PK_DashboardSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [TeamId] in table 'ReportCategorySet'
ALTER TABLE [dbo].[ReportCategorySet]
ADD CONSTRAINT [FK_TeamReportCategory]
    FOREIGN KEY ([TeamId])
    REFERENCES [dbo].[TeamSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TeamReportCategory'
CREATE INDEX [IX_FK_TeamReportCategory]
ON [dbo].[ReportCategorySet]
    ([TeamId]);
GO

-- Creating foreign key on [ReportCategoryId] in table 'ReportSet'
ALTER TABLE [dbo].[ReportSet]
ADD CONSTRAINT [FK_ReportCategoryReport]
    FOREIGN KEY ([ReportCategoryId])
    REFERENCES [dbo].[ReportCategorySet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ReportCategoryReport'
CREATE INDEX [IX_FK_ReportCategoryReport]
ON [dbo].[ReportSet]
    ([ReportCategoryId]);
GO

-- Creating foreign key on [TeamId] in table 'IssueSet'
ALTER TABLE [dbo].[IssueSet]
ADD CONSTRAINT [FK_TeamIssue]
    FOREIGN KEY ([TeamId])
    REFERENCES [dbo].[TeamSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TeamIssue'
CREATE INDEX [IX_FK_TeamIssue]
ON [dbo].[IssueSet]
    ([TeamId]);
GO

-- Creating foreign key on [TeamId] in table 'PeopleSet'
ALTER TABLE [dbo].[PeopleSet]
ADD CONSTRAINT [FK_TeamPeople]
    FOREIGN KEY ([TeamId])
    REFERENCES [dbo].[TeamSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TeamPeople'
CREATE INDEX [IX_FK_TeamPeople]
ON [dbo].[PeopleSet]
    ([TeamId]);
GO

-- Creating foreign key on [TeamId] in table 'NewsSet'
ALTER TABLE [dbo].[NewsSet]
ADD CONSTRAINT [FK_TeamNews]
    FOREIGN KEY ([TeamId])
    REFERENCES [dbo].[TeamSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TeamNews'
CREATE INDEX [IX_FK_TeamNews]
ON [dbo].[NewsSet]
    ([TeamId]);
GO

-- Creating foreign key on [TeamId] in table 'MiscSet'
ALTER TABLE [dbo].[MiscSet]
ADD CONSTRAINT [FK_TeamMisc]
    FOREIGN KEY ([TeamId])
    REFERENCES [dbo].[TeamSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TeamMisc'
CREATE INDEX [IX_FK_TeamMisc]
ON [dbo].[MiscSet]
    ([TeamId]);
GO

-- Creating foreign key on [TeamId] in table 'DashboardSet'
ALTER TABLE [dbo].[DashboardSet]
ADD CONSTRAINT [FK_TeamDashboard]
    FOREIGN KEY ([TeamId])
    REFERENCES [dbo].[TeamSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TeamDashboard'
CREATE INDEX [IX_FK_TeamDashboard]
ON [dbo].[DashboardSet]
    ([TeamId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------